package cn.chenc.framework.excel.util;

import cn.chenc.framework.excel.handler.CustomCellWriteHandler;
import cn.chenc.framework.excel.listener.ExcelListener;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.metadata.WriteSheet;
import jakarta.servlet.ServletOutputStream;
import jakarta.servlet.http.HttpServletResponse;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;

/**
 * 　@description: easyExcel工具类封装
 * 　@author 陈_C
 * 　@date 2020/8/21 11:38
 *
 */
public class EasyExcelUtil {

    //写入一个sheet最多100万数据
    private static final Integer MAX_ROWS_ONE_SHEET=1000000;

    //excel导出
    /**
     * @description: 数据导入excel文件
     * @param response
     * @param listdata   导出数据
     * @param filename   导出文件名称
     * @param clazz       导出的数据模型
     * @return void
     * @throws
     * @author 陈_C
     * @date 2020/8/21 陈_C
     */
    @SuppressWarnings({ "rawtypes", "unchecked" })
    public static void toExcle(HttpServletResponse response, List listdata, String filename, Class<?> clazz) {
        toExcle(response,listdata,filename,filename,clazz);
    }

    /**
     * @description: 数据导入excel文件
     * @param response
     * @param listdata   导出数据
     * @param filename   导出文件名称
     * @param sheetName   导出sheet名称
     * @param clazz       导出的数据模型
     * @return void
     * @throws
     * @author 陈_C
     * @date 2020/8/21 陈_C
     */
    @SuppressWarnings({ "rawtypes", "unchecked" })
    public static void toExcle(HttpServletResponse response, List listdata, String filename,String sheetName, Class<?> clazz) {
        ExcelWriter excelWriter= null;
        try {
            //设置响应格式
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String name = URLEncoder.encode(filename, "UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename="+name+".xlsx");

            //获取输出流
            ServletOutputStream outputStream= response.getOutputStream();

            //获取工作簿对象
            excelWriter = EasyExcel.write(outputStream, clazz).registerWriteHandler(new CustomCellWriteHandler()).build();

            //获取工作表数量
            Integer sheetnum=getSheetNum(listdata);
            //调用写入sheet
            writeSheet(excelWriter,listdata,sheetnum,sheetName);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            //finish 会帮忙关闭流
            if(excelWriter!=null) {
                excelWriter.finish();
            }
        }
    }

    /**
     * @description: 数据导入excel文件
     * @param listdata
     * @param pathName 文件路径
     * @param sheetName sheet名字
     * @param clazz
     * @return void
     * @throws
     * @author secret
     * @date 2021/5/13 secret
     */
    public static void toExcle(List listdata, String pathName,String sheetName, Class clazz){
        ExcelWriter excelWriter = null;
        try {
            //获取工作簿对象
            excelWriter = EasyExcel.write(pathName, clazz).registerWriteHandler(new CustomCellWriteHandler()).build();
            //获取工作表数量
            Integer sheetnum = getSheetNum(listdata);
            //调用写入sheet
            writeSheet(excelWriter,listdata,sheetnum,sheetName);
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            //finish 会帮忙关闭流
            if(excelWriter!=null) {
                excelWriter.finish();
            }
        }
    }

    /**
     * @description: 数据导入excel文件
     * @param listdata
     * @param file 写入文件
     * @param sheetName sheet名字
     * @param clazz
     * @return void
     * @throws
     * @author secret
     * @date 2021/5/13 secret
     */
    public static void toExcle(List listdata, File file, String sheetName, Class clazz){
        ExcelWriter excelWriter = null;
        try {
            //获取工作簿对象
            excelWriter = EasyExcel.write(file, clazz).registerWriteHandler(new CustomCellWriteHandler()).build();
            //获取工作表数量
            Integer sheetnum = getSheetNum(listdata);
            //调用写入sheet
            writeSheet(excelWriter,listdata,sheetnum,sheetName);
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            //finish 会帮忙关闭流
            if(excelWriter!=null) {
                excelWriter.finish();
            }
        }
    }

    /**
     * @description: excel读取
     * @param pathName
     * @param clazz
     * @return java.util.List<java.lang.Object>
     * @throws
     * @author secret
     * @date 2021/2/27 secret
     */
    public static <T> List<T> readExcel(String pathName, Class<T> clazz){
        return readExcel(pathName,0,clazz);
    }

    /**
     * @description: excel读取
     * @param pathName
     * @param sheetNo
     * @param clazz
     * @return java.util.List<java.lang.Object>
     * @throws
     * @author secret
     * @date 2021/2/27 secret
     */
    public static <T> List<T> readExcel(String pathName, Integer sheetNo, Class<T> clazz){
        ExcelListener listener = new ExcelListener();
        return readExcel(pathName,sheetNo,clazz,listener);
    }

    /**
     * @description: excel读取
     * @param pathName 文件路径
     * @param clazz
     * @param maxRows 最大读取行数
     * @return java.util.List<java.lang.Object>
     * @throws
     * @author secret
     * @date 2021/5/13 secret
     */
    public static <T> List<T> readExcel(String pathName, Class<T> clazz, Integer maxRows){
        return readExcel(pathName,0,clazz,maxRows);
    }

    /**
     * excel读取
     * @param pathName 文件路径
     * @param sheetNo
     * @param clazz
     * @param maxRows
     * @return
     */
    public static <T> List<T> readExcel(String pathName, Integer sheetNo, Class<T> clazz, Integer maxRows){
        ExcelListener listener = new ExcelListener(maxRows);
        return readExcel(pathName,sheetNo,clazz,listener);
    }


    /**
     * @description: excel读取
     * @param pathName 文件路径
     * @param sheetNo 读取指定sheet编号
     * @param clazz
     * @param listener 读取监听器
     * @return java.util.List<java.lang.Object>
     * @throws
     * @author secret
     * @date 2021/5/13 secret
     */
    public static <T> List<T> readExcel(String pathName, Integer sheetNo, Class<T> clazz, ExcelListener listener){
        ExcelReader excelReader=EasyExcel.read(pathName, clazz,listener).build();
        ReadSheet readSheet = EasyExcel.readSheet(sheetNo).build();
        excelReader.read(readSheet);
        //获取数据
        List<T> list = listener.getDatas();
        return list;
    }

    /**
     * @description: excel读取
     * @param file
     * @param clazz
     * @return java.util.List<java.lang.Object>
     * @throws
     * @author secret
     * @date 2021/2/27 secret
     */
    public static <T> List<T> readExcel(File file, Class<T> clazz){
        return readExcel(file,0,clazz);
    }

    /**
     * @description: excel读取
     * @param file
     * @param sheetNo
     * @param clazz
     * @return java.util.List<java.lang.Object>
     * @throws
     * @author secret
     * @date 2021/2/27 secret
     */
    public static <T> List<T> readExcel(File file, Integer sheetNo, Class<T> clazz){
        ExcelListener listener = new ExcelListener();
        return readExcel(file,sheetNo,clazz,listener);
    }

    /**
     * @description: excel读取
     * @param file
     * @param clazz
     * @param maxRows 最大读取行数
     * @return java.util.List<java.lang.Object>
     * @throws
     * @author secret
     * @date 2021/5/13 secret
     */
    public static <T> List<T> readExcel(File file, Class<T> clazz, Integer maxRows){
        return readExcel(file,0,clazz,maxRows);
    }

    /**
     * excel读取
     * @param file
     * @param sheetNo
     * @param clazz
     * @param maxRows
     * @return
     */
    public static <T> List<T> readExcel(File file, Integer sheetNo, Class<T> clazz, Integer maxRows){
        ExcelListener listener = new ExcelListener(maxRows);
        return readExcel(file,sheetNo,clazz,listener);
    }


    /**
     * @description: excel读取
     * @param file
     * @param sheetNo 读取指定sheet编号
     * @param clazz
     * @param listener 读取监听器
     * @return java.util.List<java.lang.Object>
     * @throws
     * @author secret
     * @date 2021/5/13 secret
     */
    public static <T> List<T> readExcel(File file, Integer sheetNo, Class<T> clazz, ExcelListener listener){
        ExcelReader excelReader=EasyExcel.read(file, clazz,listener).build();
        ReadSheet readSheet = EasyExcel.readSheet(sheetNo).build();
        excelReader.read(readSheet);
        //获取数据
        List<T> list = listener.getDatas();
        return list;
    }

    /**
     * @description: excel读取
     * @param inputStream
     * @param clazz
     * @return java.util.List<java.lang.Object>
     * @throws
     * @author secret
     * @date 2021/2/27 secret
     */
    public static <T> List<T> readExcel(InputStream inputStream, Class<T> clazz){
        return readExcel(inputStream,0,clazz);
    }

    /**
     * @description: excel读取
     * @param inputStream
     * @param sheetNo
     * @param clazz
     * @return java.util.List<java.lang.Object>
     * @throws
     * @author secret
     * @date 2021/2/27 secret
     */
    public static <T> List<T> readExcel(InputStream inputStream, Integer sheetNo, Class<T> clazz){
        ExcelListener listener = new ExcelListener();
        return readExcel(inputStream,sheetNo,clazz,listener);
    }

    /**
     * @description: excel读取
     * @param inputStream
     * @param clazz
     * @param maxRows 最大读取行数
     * @return java.util.List<java.lang.Object>
     * @throws
     * @author secret
     * @date 2021/5/13 secret
     */
    public static <T> List<T> readExcel(InputStream inputStream, Class<T> clazz, Integer maxRows){
        return readExcel(inputStream,0,clazz,maxRows);
    }

    /**
     * excel读取
     * @param inputStream
     * @param sheetNo
     * @param clazz
     * @param maxRows
     * @return
     */
    public static <T> List<T> readExcel(InputStream inputStream, Integer sheetNo, Class<T> clazz, Integer maxRows){
        ExcelListener listener = new ExcelListener(maxRows);
        return readExcel(inputStream,sheetNo,clazz,listener);
    }


    /**
     * @description: excel读取
     * @param inputStream
     * @param sheetNo 读取指定sheet编号
     * @param clazz
     * @param listener 读取监听器
     * @return java.util.List<java.lang.Object>
     * @throws
     * @author secret
     * @date 2021/5/13 secret
     */
    public static <T> List<T> readExcel(InputStream inputStream, Integer sheetNo, Class<T> clazz, ExcelListener listener){
        ExcelReader excelReader=EasyExcel.read(inputStream, clazz,listener).build();
        ReadSheet readSheet = EasyExcel.readSheet(sheetNo).build();
        excelReader.read(readSheet);
        //获取数据
        List<T> list = listener.getDatas();
        return list;
    }

    /**
     * 计算Sheet数量
     * @param listdata
     * @return
     */
    private static Integer getSheetNum(List<Object> listdata){
        if (listdata.size()<MAX_ROWS_ONE_SHEET) {
            return 1;
        }else {
            return listdata.size()%MAX_ROWS_ONE_SHEET==0 ? listdata.size()/MAX_ROWS_ONE_SHEET :listdata.size()/MAX_ROWS_ONE_SHEET+1;//这里我是根据自身工作需要设置每一百万条数据一个sheet
        }
    }

    /**
     * @description: 写入sheet
     * @param excelWriter
     * @param listdata 需要写入的数据
     * @param sheetNum sheet数量
     * @param sheetName sheet名称
     * @return void
     * @throws
     * @author secret
     * @date 2021/5/13 secret
     */
    private static void writeSheet(ExcelWriter excelWriter,List<Object> listdata,Integer sheetNum,String sheetName){
        // 去调用写入,这里我调用了sheetnum次，实际使用时根据数据库分页的总的页数来。这里最终会写到sheetnum个sheet里面
        List<Object> subList = null;
        for (int i = 0; i < sheetNum; i++) {
            // 每次都要创建writeSheet 这里注意必须指定sheetNo
            WriteSheet writeSheet = EasyExcel.writerSheet(i, sheetName + "(" + i + ")").build();
            //这里是将查询的数据分为若干个sheet页
            subList = listdata.subList(i * MAX_ROWS_ONE_SHEET, i == sheetNum - 1 ? listdata.size() : (i + 1) * MAX_ROWS_ONE_SHEET);
            excelWriter.write(subList, writeSheet);
        }
    }

}
